CREATE PROCEDURE dbo.pa_AmparoLegalidad_Fecha

-- Consulta/Reporte Amparos de Legalidad
-- solicitada por Ana Lorena Brenes 2011-03-22

-- Parametros 

@Fecha_Ingreso_CHAR VARCHAR(10)	-- yyyy-mm-dd

AS 

DECLARE
@Fecha_Ingreso smalldatetime

---------------------------------------------------
-- PASO 1 : Recupera los datos de los expedienes --
---------------------------------------------------
SELECT @Fecha_Ingreso = CONVERT( smalldatetime , @Fecha_Ingreso_CHAR , 121 )

SELECT	e.Expediente , 
	Resolucion = CONVERT( varchar(19) , ( SELECT MIN( Fecha_PGR ) FROM Exp_Fecha AS n WHERE e.Expediente = n.Expediente ) , 121 ) , 
	Nombre_Procurador = LEFT( v.Nombre_Procurador , 35 ) , 
	Partes = RTRIM( e.Promovente ) + ' CONTRA ' + RTRIM( e.Parte ) , 
	MateriaTipoAsunto = LEFT( m.nombre , 25 ) ,
	Institucion_Involucrada = SPACE(1000) , 
	Institucion = x.Area
INTO	#Tabla
FROM	Expediente AS e
	INNER JOIN procuradores_x_Expediente AS x ON e.expediente = x.expediente and x.primario_chk = '1' 
	INNER JOIN View_Nombre_Procuradores AS v ON x.procurador = v.procurador 
	INNER JOIN materiatipoasunto AS m ON x.materiatipoasunto = m.materiatipoasunto AND m.materiatipoasunto  IN ( 11001 , 11301 )
WHERE	( YEAR( Fecha_Ing_PGR ) * 10000 ) + ( MONTH( Fecha_Ing_PGR ) * 100 ) + DAY( Fecha_Ing_PGR )
	= 
	( YEAR( @Fecha_Ingreso ) * 10000 ) + ( MONTH( @Fecha_Ingreso ) * 100 ) + DAY( @Fecha_Ingreso )
ORDER BY Nombre_Procurador

------------------------------------------------------
-- PASO 2 : Recupera la primera Resolucion recibida --
------------------------------------------------------
UPDATE t SET t.Resolucion = n.Resolucion 
FROM #Tabla t , Exp_Fecha n
WHERE t.Expediente = n.Expediente AND t.Resolucion = CONVERT( varchar(19) , n.Fecha_PGR , 121 )

------------------------------------------------------
-- PASO 2 : Recupera las Instituciones Involucradas --
------------------------------------------------------
--UPDATE #Tabla SET Institucion = ( SELECT x.InstitucionInvolucrada FROM Instituciones_x_Expediente x WHERE x.Expediente = #Tabla.Expediente  )
--UPDATE #Tabla SET Institucion_Involucrada = ( SELECT i.Nombre FROM InstitucionInvolucrada i WHERE i.InstitucionInvolucrada = #Tabla.Institucion )
UPDATE #Tabla SET Institucion_Involucrada = LTRIM( Institucion_Involucrada )
DECLARE Consulta CURSOR FOR
	SELECT 	x.Expediente ,
		x.InstitucionInvolucrada
	FROM 	Instituciones_x_Expediente AS x 
	WHERE 	x.Expediente in ( SELECT Expediente FROM #Tabla )
OPEN Consulta
DECLARE @Expediente varchar(20) , @InstitucionInvolucrada int
FETCH NEXT FROM Consulta INTO @Expediente , @InstitucionInvolucrada
WHILE (@@fetch_status <> -1)
	BEGIN
	UPDATE #Tabla SET Institucion_Involucrada  = Institucion_Involucrada + ', ' + 
		RTRIM( ( SELECT i.Nombre FROM InstitucionInvolucrada i WHERE i.InstitucionInvolucrada = @InstitucionInvolucrada ) ) 
		WHERE Expediente = @Expediente
	FETCH NEXT FROM Consulta INTO @Expediente , @InstitucionInvolucrada
	END
DEALLOCATE Consulta
UPDATE #Tabla SET Institucion_Involucrada = RIGHT( Institucion_Involucrada , LEN( Institucion_Involucrada ) - 2 )

SELECT Expediente , Resolucion , Nombre_Procurador , Partes , MateriaTipoAsunto , Institucion_Involucrada
FROM #Tabla
